package ch.sv7.tool.dbmanager.integration.sybase;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.PrintStream;
import java.net.URL;
import java.sql.Connection;

import junit.framework.TestCase;

import org.apache.tools.ant.util.FileUtils;

import ch.sv7.tool.dbmanager.Context;
import ch.sv7.tool.dbmanager.db.DatabaseSchema;
import ch.sv7.tool.dbmanager.db.dialect.Dialect;
import ch.sv7.tool.dbmanager.db.dialect.DialectFactory;
import ch.sv7.tool.dbmanager.db.difference.SchemaDifferences;
import ch.sv7.tool.dbmanager.db.fix.SchemaFixes;
import ch.sv7.tool.dbmanager.db.sql.SQLDatabaseSchemaFactory;
import ch.sv7.tool.dbmanager.db.xml.XmlDatabaseSchemaFactory;
import ch.sv7.tool.dbmanager.types.TypesInfo;
import ch.sv7.tool.dbmanager.types.TypesInfoFactory;
import ch.sv7.tool.dbmanager.util.SQLHelper;
import ch.sv7.tool.dbmanager.util.ScenarioReader;
import ch.sv7.tool.log.Appender;
import ch.sv7.tool.log.DefaultAppender;
import ch.sv7.tool.log.Logger;

/**
 * TODO: Document me!
 *
 * @author svt
 *
 */
public class SybaseMigrationScenariiTestCase extends TestCase {

	private static final String SCENARII_PATH_PREFIX = "/ch/sv7/tool/dbmanager/db/sybase/scenarii/";
	private Connection connection;
	private Dialect dialect;
	private TypesInfo typesInfo;
	private Context context;
	private File scenarioFile;
	private File logFolder;
	
	protected void setUp() throws Exception {
		super.setUp();
		init();
	}
	
	protected void init() throws Exception {
		Logger.setAppender(new DefaultAppender(Appender.LEVEL_INFO));
		connection = ConnectionFactory.getTestConnection();
		typesInfo = TypesInfoFactory.getInstance().getTypesInfo("sybase", connection);
		dialect = DialectFactory.getInstance().getDialect("sybase", typesInfo);
		context = new Context(dialect, null);
		
		try {
			connection.setAutoCommit(true);
		} catch (Exception e) {
			// ignore
		}		
	}
	
	public void dropDatabase() {
		try {
			SQLHelper.executeSQL("drop table jdbc_manager_test_a", getConnection());
		} catch (Exception e) {
			// ignore
		}
		try {
			SQLHelper.executeSQL("drop table jdbc_manager_test_b", getConnection());
		} catch (Exception e) {
			// ignore
		}
		try {
			SQLHelper.executeSQL("drop table jdbc_manager_test_c", getConnection());
		} catch (Exception e) {
			// ignore
		}
		
	}
	
	public Dialect getDialect() {
		return dialect;
	}
	
	public Context getContext() {
		return context;
	}
	
	public TypesInfo getTypesInfo() {
		return typesInfo;
	}
	
	public Connection getConnection(){
		return connection;
	}
	
	protected void tearDown() throws Exception {
		getConnection().close();
		super.tearDown();
	}
	
	protected void initLogFolder(boolean withData) {
		String currentTest = getName();
		currentTest = currentTest.substring("test".length());
		logFolder = new File(scenarioFile.getParentFile(), "logs/" + currentTest + "/" + (withData ? "with-data" : "no-data"));
		if (logFolder.exists()) {
			FileUtils.delete(logFolder);
		}
		logFolder.mkdirs();
	}
	/*
	 * 		Logger.setAppender(new DefaultAppender(Appender.LEVEL_DEBUG));
	 */
		
	private PrintStream getPrintStream(String name) throws FileNotFoundException {
		File logFile = new File(logFolder, name + ".log");
		//System.out.println("Logging to : " + logFile.getAbsolutePath());
		PrintStream stream = new PrintStream(new FileOutputStream(logFile));
		return stream;
	}
	
	public void executeTest(boolean insertData) throws Exception {
		dropDatabase();
		String currentTest = getName();
		currentTest = currentTest.substring("test".length());
		String scenarioPath = SCENARII_PATH_PREFIX + currentTest + ".xml";
		URL url = this.getClass().getResource(scenarioPath);
		scenarioFile = new File(url.getFile());
		
		initLogFolder(insertData);
		
		assertNotNull("Unable to find scenario : " + scenarioPath, url);	
		
		ScenarioReader reader = new ScenarioReader(url);
		
		assertNotNull(reader.getBeforeDatabaseStream());
		assertNotNull(reader.getAfterDatabaseStream());

		/*
		 * Setup the database
		 */
		XmlDatabaseSchemaFactory factory = 
			new XmlDatabaseSchemaFactory(reader.getBeforeDatabaseStream(), null);
		DatabaseSchema beforeXmlSchema = factory.getDatabaseSchema();
		SQLDatabaseSchemaFactory sqlFactory = new SQLDatabaseSchemaFactory(getConnection(), getDialect());
		DatabaseSchema sqlSchema = sqlFactory.getDatabaseSchema();
		
		SchemaDifferences diffs = new SchemaDifferences();
		
		beforeXmlSchema.computeDifferencesFromActual(diffs, sqlSchema);
		SchemaFixes fixes = new SchemaFixes(getDialect(), getContext(), diffs);
		PrintStream initialLog = getPrintStream("db-setup");
		fixes.apply(getConnection(), initialLog);
		initialLog.close();
		
		sqlSchema = sqlFactory.getDatabaseSchema();
		diffs = new SchemaDifferences();
		beforeXmlSchema.computeDifferencesFromActual(diffs, sqlSchema);
		assertEquals("Unable to setup the tables correctly", 0, diffs.getBlockingDifferencesSize());

		XmlDatabaseSchemaFactory afterFactory = 
			new XmlDatabaseSchemaFactory(reader.getAfterDatabaseStream(), null);
		DatabaseSchema afterXmlSchema = afterFactory.getDatabaseSchema();

		
		/*
		 * Insert data
		 */
		if (insertData) {
			SQLHelper.executeMultiSQL(reader.getSql(), getConnection());
			sqlSchema = sqlFactory.getDatabaseSchema();
		}
		
		/*
		 * Migrate database
		 */
		diffs = new SchemaDifferences();
		afterXmlSchema.computeDifferencesFromActual(diffs, sqlSchema);
		PrintStream diffStream = getPrintStream("migration-diffs");
		diffs.dump(diffStream);
		diffStream.close();
		fixes = new SchemaFixes(getDialect(), getContext(), diffs);
		PrintStream migrationLog = getPrintStream("migration");
		fixes.apply(getConnection(), migrationLog);
		migrationLog.close();
		
		sqlSchema = sqlFactory.getDatabaseSchema();
		diffs = new SchemaDifferences();
		afterXmlSchema.computeDifferencesFromActual(diffs, sqlSchema);
		
		assertEquals("Migration failed, there is blocking differences", 0, diffs.getBlockingDifferencesSize());

		if (reader.isReversible()){
			diffs = new SchemaDifferences();
			beforeXmlSchema.computeDifferencesFromActual(diffs, sqlSchema);
			
			PrintStream revertDiffStream = getPrintStream("migration-back-diffs");
			diffs.dump(revertDiffStream);
			revertDiffStream.close();
			
			fixes = new SchemaFixes(getDialect(), getContext(), diffs);
			PrintStream migrationRevertLog = getPrintStream("migration-back");
			fixes.apply(getConnection(), migrationRevertLog);
			migrationRevertLog.close();
			
			sqlSchema = sqlFactory.getDatabaseSchema();
			diffs = new SchemaDifferences();
			beforeXmlSchema.computeDifferencesFromActual(diffs, sqlSchema);
			
			assertEquals("Migration back failed, there is blocking differences", 0, diffs.getBlockingDifferencesSize());
			
			
			
		}
		
	}
	
	public void testAddRemoveColumn() throws Exception {
		executeTest(false);
		executeTest(true);
	}

	public void testAddRemoveIndex() throws Exception {
		executeTest(false);
		executeTest(true);
	}
	
	public void testAddRemoveForeignKey() throws Exception {
		executeTest(false);
		executeTest(true);
	}

	public void testAddRemoveConstraint() throws Exception {
		executeTest(false);
		executeTest(true);
	}
	
	public void testCustomConversion() throws Exception {
		executeTest(false);
		executeTest(true);
	}
}
